<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <title>mongodb笔记(五) 索引 | 净土</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="mongodb indexing, mongodb 索引">
<meta property="og:type" content="article">
<meta property="og:title" content="mongodb笔记(五) 索引">
<meta property="og:url" content="http://howiefh.github.io/2014/05/26/mongodb-note-5-indexing/index.html">
<meta property="og:site_name" content="净土">
<meta property="og:description" content="mongodb indexing, mongodb 索引">
<meta property="og:updated_time" content="2014-12-24T07:55:07.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="mongodb笔记(五) 索引">
<meta name="twitter:description" content="mongodb indexing, mongodb 索引">
  
    <link rel="alternative" href="/atom.xml" title="净土" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.ico">
  
  <link href="http://fonts.useso.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  <link rel="stylesheet" href="/css/style.css" type="text/css">
  
<script type="text/javascript">
(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,'script','//www.google-analytics.com/analytics.js','ga');

ga('create', 'UA-40492061-1', 'auto');
ga('send', 'pageview');

</script>


  
<script>
var _hmt = _hmt || [];
(function() {
  var hm = document.createElement("script");
  hm.src = "//hm.baidu.com/hm.js?56d2899c5e919fbf4a7b00de5d1c31dd";
  var s = document.getElementsByTagName("script")[0]; 
  s.parentNode.insertBefore(hm, s);
})();
</script>


</head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/" id="logo">净土</a>
      </h1>
      
        <h2 id="subtitle-wrap">
          <a href="/" id="subtitle">乐不在外而在心，心以为乐，则是境皆乐；心以为苦，则无境不苦。</a>
        </h2>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/">Home</a>
        
          <a class="main-nav-link" href="/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-github-link" class="nav-icon" href="https://github.com/howiefh" title="Github" target="_blank"></a>
        
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed" target="_blank"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Search"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" results="0" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://howiefh.github.io"></form>
      </div>
    </div>
  </div>
</header>

      <div class="outer">
        <section id="main"><article id="post-mongodb-note-5-indexing" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    
<a href="/2014/05/26/mongodb-note-5-indexing/" class="article-date">
  <time datetime="2014-05-26T03:48:34.000Z" itemprop="datePublished">2014-05-26</time>
</a>


    
  <div class="article-category">
    <a class="article-category-link" href="/categories/Database/">Database</a>►<a class="article-category-link" href="/categories/Database/MongoDB/">MongoDB</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      mongodb笔记(五) 索引
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
		
		<div id="toc" class="toc-article">
			<h2 class="toc-title"><span>Contents</span></h2>
		
			<ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#索引简介"><span class="toc-number">1.</span> <span class="toc-text">索引简介</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#扩展索引"><span class="toc-number">1.1.</span> <span class="toc-text">扩展索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#索引内建文档的键"><span class="toc-number">1.2.</span> <span class="toc-text">索引内建文档的键</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#为排序创建索引"><span class="toc-number">1.3.</span> <span class="toc-text">为排序创建索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#索引名称"><span class="toc-number">1.4.</span> <span class="toc-text">索引名称</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#唯一索引"><span class="toc-number">2.</span> <span class="toc-text">唯一索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#消除重复"><span class="toc-number">2.1.</span> <span class="toc-text">消除重复</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#复合唯一索引"><span class="toc-number">2.2.</span> <span class="toc-text">复合唯一索引</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#使用explain和hint"><span class="toc-number">3.</span> <span class="toc-text">使用explain和hint</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#索引管理"><span class="toc-number">4.</span> <span class="toc-text">索引管理</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#修改索引"><span class="toc-number">4.1.</span> <span class="toc-text">修改索引</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#地理空间索引"><span class="toc-number">5.</span> <span class="toc-text">地理空间索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#两种方式查询"><span class="toc-number">5.1.</span> <span class="toc-text">两种方式查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#按形状查询"><span class="toc-number">5.2.</span> <span class="toc-text">按形状查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#复合地理空间索引"><span class="toc-number">5.3.</span> <span class="toc-text">复合地理空间索引</span></a></li></ol></li></ol>
		
		</div>
		
        <h2 id="索引简介">索引简介</h2><p>创建单键索引<br><figure class="highlight stylus"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.<span class="function"><span class="title">ensureIndex</span><span class="params">(&#123;<span class="string">"date"</span>:<span class="number">1</span>&#125;)</span></span></span><br></pre></td></tr></table></figure></p>
<p>创建复合索引<br><figure class="highlight stylus"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.<span class="function"><span class="title">ensureIndex</span><span class="params">(&#123;<span class="string">"date"</span>:<span class="number">1</span>,<span class="string">"username"</span>:<span class="number">1</span>&#125;)</span></span></span><br></pre></td></tr></table></figure></p>
<p>ensureIndex的文档形式和sort类似。</p>
<a id="more"></a>
<p>创建下面索引<br><figure class="highlight stylus"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.<span class="function"><span class="title">ensureIndex</span><span class="params">(&#123;<span class="string">"a"</span>:<span class="number">1</span>,<span class="string">"b"</span>:<span class="number">1</span>,<span class="string">"c"</span>:<span class="number">1</span>,...,<span class="string">"z"</span>:<span class="number">1</span>&#125;)</span></span></span><br></pre></td></tr></table></figure></p>
<p>也就有了{“a”:1}、{“a”:1,”b”:1}、{“a”:1,”b”:1,”c”:1}等索引，但是{“b”:1}、{“a”:1,”c”:1}等索引不存在。</p>
<p>mongodb查询优化器能够重排查询项的顺序，以便利用索引，比如查询{“x”:”foo”,”y”:”bar”}的时候，已经有了{“y”:1,”x”:1}的索引，mongodb会自己找到并利用它。</p>
<h3 id="扩展索引">扩展索引</h3><p>创建索引时应该考虑下面问题:</p>
<ol>
<li>会做什么样的查询？其中哪些键需要索引？</li>
<li>每个键的索引方向是怎样的？</li>
<li>如何应对扩展？有没有种不同的键的排列可以使常用数据更多地保留在内存中？</li>
</ol>
<p>如果将索引{“user”:1,”date”:1}变为{“date”:-1,”user”:1}则数据库可以将最后几天的索引保存在内存中。</p>
<h3 id="索引内建文档的键">索引内建文档的键</h3><p>可以由内嵌的”comments”文档组成的数组中对”date”键创建索引<br><figure class="highlight openscad"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.blog.ensureIndex<span class="params">(&#123;<span class="string">"comments.date"</span>:<span class="number">1</span>&#125;)</span></span><br></pre></td></tr></table></figure></p>
<h3 id="为排序创建索引">为排序创建索引</h3><p>做无索引排序是有上限的，需要针对一些查询中的排序做索引。</p>
<h3 id="索引名称">索引名称</h3><p>默认情况下，索引名类似keyname1_dir1_keyname2_dir2…_keynameN_dirN。<br>其中keynameX代表索引的键，dirX代表索引方向(1、-1)。可以指定索引名<br><figure class="highlight lasso"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db<span class="built_in">.</span>foo<span class="built_in">.</span>ensureIndex(&#123;<span class="string">"a"</span>:<span class="number">1</span>,<span class="string">"b"</span>:<span class="number">1</span>,<span class="string">"c"</span>:<span class="number">1</span>,<span class="attribute">...</span>,<span class="string">"z"</span>:<span class="number">1</span>&#125;,&#123;<span class="string">"name"</span>:<span class="string">"alphabet"</span>&#125;)</span><br></pre></td></tr></table></figure></p>
<h2 id="唯一索引">唯一索引</h2><p>唯一索引可以确保每个键都有唯一值。<br><figure class="highlight openscad"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.people.ensureIndex<span class="params">(&#123;<span class="string">"username"</span>:<span class="number">1</span>&#125;,&#123;<span class="string">"unique"</span>:<span class="literal">true</span>&#125;)</span></span><br></pre></td></tr></table></figure></p>
<p>如果没有对应的键，索引会将其作为null存储，如果对某个键建立了唯一索引，但插入了多个缺少该索引键的文档，则由于文档包含null值而导致失败。</p>
<h3 id="消除重复">消除重复</h3><figure class="highlight openscad"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.people.ensureIndex<span class="params">(&#123;<span class="string">"username"</span>:<span class="number">1</span>&#125;,&#123;<span class="string">"unique"</span>:<span class="literal">true</span>,<span class="string">"dropDups"</span>:<span class="literal">true</span>&#125;)</span></span><br></pre></td></tr></table></figure>
<p>dropDups 可以保留发现的第一个文档，删除接下来的重复值的文档。</p>
<h3 id="复合唯一索引">复合唯一索引</h3><p>创建复合唯一索引的时候，单个键的值可以相同，只要组合起来不同就好了。</p>
<h2 id="使用explain和hint">使用explain和hint</h2><figure class="highlight stylus"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db<span class="class">.foo</span><span class="class">.find</span>().<span class="function"><span class="title">explain</span><span class="params">()</span></span></span><br></pre></td></tr></table></figure>
<p>可以获取查询的信息</p>
<ul>
<li>cursor: 有没有使用索引</li>
<li>nscanned: 查找了多少文档</li>
<li>n: 返回文档的个数</li>
<li>millis: 查询时间</li>
</ul>
<p>hint可以用来强制指定使用某个索引<br><figure class="highlight gradle"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.c.<span class="keyword">find</span>(&#123;<span class="string">"age"</span>:<span class="number">14</span>,<span class="string">"username"</span>:<span class="regexp">/.*/</span>&#125;).hint(&#123;<span class="string">"username"</span>:<span class="number">1</span>,<span class="string">"age"</span>:<span class="number">1</span>&#125;)</span><br></pre></td></tr></table></figure></p>
<p>但是多数情况下没有必要指定使用哪个索引，mongodb的查询优化器很智能。</p>
<h2 id="索引管理">索引管理</h2><p>索引的元信息存储在每个数据库的system.indexes集合中。这是一个保留集合，不能对其插入或者删除文档。操作只能通过ensureIndex或者dropIndexes进行。</p>
<p>system.indexes集合包含每个索引的详细信息，同时system.namespaces集合也含有索引的名字。</p>
<h3 id="修改索引">修改索引</h3><figure class="highlight openscad"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.people.ensureIndex<span class="params">(&#123;<span class="string">"username"</span>:<span class="number">1</span>&#125;,&#123;<span class="string">"background"</span>:<span class="literal">true</span>&#125;)</span></span><br></pre></td></tr></table></figure>
<p>向现有集合添加新的索引，可以通过使用background选项是任务在后台执行。</p>
<figure class="highlight stylus"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">db.<span class="function"><span class="title">runCommand</span><span class="params">(&#123;<span class="string">"dropIndexes"</span>:<span class="string">"foo"</span>,<span class="string">"index"</span>:<span class="string">"alphabet"</span>&#125;)</span></span></span><br><span class="line">db.<span class="function"><span class="title">runCommand</span><span class="params">(&#123;<span class="string">"dropIndexes"</span>:<span class="string">"foo"</span>,<span class="string">"index"</span>:<span class="string">"*"</span>&#125;)</span></span></span><br></pre></td></tr></table></figure>
<p>通过dropIndexes可以删除索引，通过<code>*</code>删除所有索引</p>
<h2 id="地理空间索引">地理空间索引</h2><figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.<span class="built_in">map</span>.ensureIndex(&#123;<span class="string">"gps"</span>:<span class="string">"2d"</span>&#125;)</span><br></pre></td></tr></table></figure>
<p><code>gps</code>键的值必须是某种形式的一对值：一个包含两个元素的数组或者包含两个键的文档。<br><figure class="highlight json"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">&#123;"<span class="attribute">gps</span>":<span class="value">[<span class="number">0</span>,<span class="number">100</span>]</span>&#125;</span><br><span class="line">&#123;"<span class="attribute">gps</span>":<span class="value">&#123;"<span class="attribute">x</span>":<span class="value">-<span class="number">30</span></span>,"<span class="attribute">y</span>":<span class="value"><span class="number">30</span></span>&#125;</span>&#125;</span><br><span class="line">&#123;"<span class="attribute">gps</span>":<span class="value">&#123;"<span class="attribute">latitude</span>":<span class="value">-<span class="number">100</span></span>,"<span class="attribute">longitude</span>":<span class="value"><span class="number">180</span></span>&#125;</span>&#125;</span><br></pre></td></tr></table></figure></p>
<p>以上形式都是合法的。</p>
<p>默认情况下地理空间索引假设值的范围是-180~180。也可以指定最大最小值，下面创建了一个2000光年的空间索引<br><figure class="highlight elixir"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.star.trek.ensureIndex(&#123;<span class="string">"light-years"</span><span class="symbol">:<span class="string">"2d"</span></span>&#125;,&#123;<span class="string">"min"</span><span class="symbol">:-</span><span class="number">1000</span>,<span class="string">"max"</span><span class="symbol">:</span><span class="number">1000</span>&#125;)</span><br></pre></td></tr></table></figure></p>
<h3 id="两种方式查询">两种方式查询</h3><ul>
<li>$near</li>
</ul>
<figure class="highlight perl"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.<span class="keyword">map</span>.find(&#123;<span class="string">"gps"</span>:&#123;<span class="string">"<span class="variable">$near</span>"</span>:&#123;<span class="number">40</span>,-<span class="number">73</span>&#125;&#125;&#125;)</span><br></pre></td></tr></table></figure>
<p>这会按照离点(40,-73)由近及远的方式将map的所有文档返回。</p>
<ul>
<li>geoNear</li>
</ul>
<figure class="highlight less"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag">db</span><span class="class">.runCommand</span>(&#123;<span class="attribute">geoNear</span>:<span class="string">"map"</span>,<span class="attribute">near</span>:[<span class="number">40</span>,-<span class="number">73</span>],<span class="attribute">num</span>:<span class="number">10</span>&#125;)</span><br></pre></td></tr></table></figure>
<p>geoNear还会返回每个文档到查询点的距离。</p>
<h3 id="按形状查询">按形状查询</h3><p>对于矩形<br><figure class="highlight lua"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.map.find(&#123;<span class="string">"gps"</span>:&#123;<span class="string">"$within"</span>:&#123;<span class="string">"$box"</span>:<span class="string">[[10,20],[15,30]]</span>&#125;&#125;&#125;)</span><br></pre></td></tr></table></figure></p>
<p>第一元素是左下角坐标，第二个元素是右上角坐标。</p>
<p>对于圆<br><figure class="highlight perl"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.<span class="keyword">map</span>.find(&#123;<span class="string">"gps"</span>:&#123;<span class="string">"<span class="variable">$within</span>"</span>:&#123;<span class="string">"<span class="variable">$center</span>"</span>:[[<span class="number">12</span>,<span class="number">25</span>],<span class="number">5</span>]&#125;&#125;&#125;)</span><br></pre></td></tr></table></figure></p>
<p>第一个元素是圆心坐标，第二个是半径</p>
<h3 id="复合地理空间索引">复合地理空间索引</h3><p>如果查询附近所有咖啡馆，可以创建复合索引<br><figure class="highlight stylus"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.<span class="function"><span class="title">ensureIndex</span><span class="params">(&#123;<span class="string">"location"</span>:<span class="string">"2d"</span>,<span class="string">"desc"</span>:<span class="number">1</span>&#125;)</span></span></span><br></pre></td></tr></table></figure></p>
<p>然后查找最近的咖啡馆<br><figure class="highlight openscad"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">db.map.find<span class="params">(&#123;<span class="string">"location"</span>:&#123;<span class="string">"$near"</span>:[-<span class="number">70</span>,<span class="number">30</span>]&#125;,<span class="string">"desc"</span>:<span class="string">"coffeeshop"</span>&#125;)</span>.limit<span class="params">(<span class="number">1</span>)</span></span><br></pre></td></tr></table></figure></p>

      
    </div>
    <footer class="article-footer">
	  
	  <!-- 百度分享 Start -->
	  <div class="bdsharebuttonbox"><a href="#" class="bds_more" data-cmd="more"></a><a href="#" class="bds_qzone" data-cmd="qzone" title="分享到QQ空间"></a><a href="#" class="bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a><a href="#" class="bds_tqq" data-cmd="tqq" title="分享到腾讯微博"></a><a href="#" class="bds_renren" data-cmd="renren" title="分享到人人网"></a><a href="#" class="bds_weixin" data-cmd="weixin" title="分享到微信"></a></div>
	  <!-- 百度分享 End -->
	  
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/MongoDB/">MongoDB</a></li></ul>

	  
<span>
Updated:<time datetime="2014-12-24T07:55:07.000Z" itemprop="dateModified">2014-12-24</time>
</span>


    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2014/05/28/mongodb-note-6-aggregation/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          mongodb笔记(六) 聚合
        
      </div>
    </a>
  
  
    <a href="/2014/05/08/mongodb-note-4-querying/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">mongodb笔记(四) 查询</div>
    </a>
  
</nav>

  
</article>



<!-- 多说评论框 start -->

<section id="comments">
  <div class="ds-thread"  data-thread-key="/2014/05/26/mongodb-note-5-indexing/" data-title="mongodb笔记(五) 索引" data-url="http://howiefh.github.io/2014/05/26/mongodb-note-5-indexing/" id="ds_thread">
    <noscript>Please enable JavaScript to view the <a href="//duoshuo.com/?ref_noscript">comments powered by duoshuo.</a></noscript>
  </div>
</section>

<!-- 多说评论框 end -->
</section>
        
          
  <div id="toc" class="toc-aside">
  <h2 class="toc-title">Contents</h2>
    
        <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#索引简介"><span class="toc-number">1.</span> <span class="toc-text">索引简介</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#扩展索引"><span class="toc-number">1.1.</span> <span class="toc-text">扩展索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#索引内建文档的键"><span class="toc-number">1.2.</span> <span class="toc-text">索引内建文档的键</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#为排序创建索引"><span class="toc-number">1.3.</span> <span class="toc-text">为排序创建索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#索引名称"><span class="toc-number">1.4.</span> <span class="toc-text">索引名称</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#唯一索引"><span class="toc-number">2.</span> <span class="toc-text">唯一索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#消除重复"><span class="toc-number">2.1.</span> <span class="toc-text">消除重复</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#复合唯一索引"><span class="toc-number">2.2.</span> <span class="toc-text">复合唯一索引</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#使用explain和hint"><span class="toc-number">3.</span> <span class="toc-text">使用explain和hint</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#索引管理"><span class="toc-number">4.</span> <span class="toc-text">索引管理</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#修改索引"><span class="toc-number">4.1.</span> <span class="toc-text">修改索引</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#地理空间索引"><span class="toc-number">5.</span> <span class="toc-text">地理空间索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#两种方式查询"><span class="toc-number">5.1.</span> <span class="toc-text">两种方式查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#按形状查询"><span class="toc-number">5.2.</span> <span class="toc-text">按形状查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#复合地理空间索引"><span class="toc-number">5.3.</span> <span class="toc-text">复合地理空间索引</span></a></li></ol></li></ol>
    
  </div>

<aside id="sidebar">

  
    
<div class="widget-wrap">
  <h3 class="widget-title">ABOUT ME</h3>
  <ul class="widget about-me">
    
    <li><img class="author" title="About me" src="http://fh-1.qiniudn.com/okal-eltocat.jpg" /></li>
    
    
    <li>Hi,I'm FengHao.</li>
    
    <li>I'll share something interesting and my learning experience with you at this blog.</li>
    
    <li>前博客:<a href="http://hi.baidu.com/idea_star" target="_BLANK">百度空间</a></li>
    
  </ul>
</div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Categories</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/Android/">Android</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/C/">C</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Database/">Database</a><span class="category-list-count">13</span><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/categories/Database/MongoDB/">MongoDB</a><span class="category-list-count">10</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Database/MySQL/">MySQL</a><span class="category-list-count">2</span></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/categories/Eclipse/">Eclipse</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/FTP/">FTP</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Git/">Git</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Hexo/">Hexo</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Java/">Java</a><span class="category-list-count">20</span><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/categories/Java/FreeMarker/">FreeMarker</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Java/Shiro/">Shiro</a><span class="category-list-count">2</span></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/categories/JavaEE/">JavaEE</a><span class="category-list-count">4</span><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/categories/JavaEE/Hibernate/">Hibernate</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/JavaEE/JSP/">JSP</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/JavaEE/Spring/">Spring</a><span class="category-list-count">2</span></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/categories/JavaScript/">JavaScript</a><span class="category-list-count">5</span><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/categories/JavaScript/jQuery/">jQuery</a><span class="category-list-count">1</span></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/categories/Linux/">Linux</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/PHP/">PHP</a><span class="category-list-count">5</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Suse/">Suse</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Ubuntu/">Ubuntu</a><span class="category-list-count">5</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Vim/">Vim</a><span class="category-list-count">8</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/编程/">编程</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/软件/">软件</a><span class="category-list-count">1</span></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/tags/Android/" style="font-size: 14.75px; color: #65bfa7">Android</a> <a href="/tags/C/" style="font-size: 13.88px; color: #71c1c2">C</a> <a href="/tags/CAS/" style="font-size: 13px; color: #7dc3de">CAS</a> <a href="/tags/Chrome/" style="font-size: 13px; color: #7dc3de">Chrome</a> <a href="/tags/Clean-Code/" style="font-size: 13.88px; color: #71c1c2">Clean Code</a> <a href="/tags/Database/" style="font-size: 13px; color: #7dc3de">Database</a> <a href="/tags/Eclipse/" style="font-size: 13px; color: #7dc3de">Eclipse</a> <a href="/tags/FTP/" style="font-size: 13.88px; color: #71c1c2">FTP</a> <a href="/tags/FreeMarker/" style="font-size: 14.75px; color: #65bfa7">FreeMarker</a> <a href="/tags/Gcc/" style="font-size: 13px; color: #7dc3de">Gcc</a> <a href="/tags/Git/" style="font-size: 14.75px; color: #65bfa7">Git</a> <a href="/tags/Github-Pages/" style="font-size: 13px; color: #7dc3de">Github Pages</a> <a href="/tags/Hexo/" style="font-size: 14.75px; color: #65bfa7">Hexo</a> <a href="/tags/Hibernate/" style="font-size: 13px; color: #7dc3de">Hibernate</a> <a href="/tags/JSP/" style="font-size: 13px; color: #7dc3de">JSP</a> <a href="/tags/JVM/" style="font-size: 14.75px; color: #65bfa7">JVM</a> <a href="/tags/Java/" style="font-size: 14.75px; color: #65bfa7">Java</a> <a href="/tags/JavaMail/" style="font-size: 13px; color: #7dc3de">JavaMail</a> <a href="/tags/JavaScript/" style="font-size: 16.5px; color: #4dbc6f">JavaScript</a> <a href="/tags/Linux/" style="font-size: 13.88px; color: #71c1c2">Linux</a> <a href="/tags/Log/" style="font-size: 13px; color: #7dc3de">Log</a> <a href="/tags/Markdown/" style="font-size: 13.88px; color: #71c1c2">Markdown</a> <a href="/tags/MongoDB/" style="font-size: 20px; color: #1db400">MongoDB</a> <a href="/tags/MySQL/" style="font-size: 13.88px; color: #71c1c2">MySQL</a> <a href="/tags/PHP/" style="font-size: 16.5px; color: #4dbc6f">PHP</a> <a href="/tags/Rhythmbox/" style="font-size: 13px; color: #7dc3de">Rhythmbox</a> <a href="/tags/SSO/" style="font-size: 13px; color: #7dc3de">SSO</a> <a href="/tags/Servlet/" style="font-size: 13px; color: #7dc3de">Servlet</a> <a href="/tags/Shiro/" style="font-size: 13.88px; color: #71c1c2">Shiro</a> <a href="/tags/Spring/" style="font-size: 13.88px; color: #71c1c2">Spring</a> <a href="/tags/Suse/" style="font-size: 13px; color: #7dc3de">Suse</a> <a href="/tags/Thinking-in-Java/" style="font-size: 19.13px; color: #29b61c">Thinking in Java</a> <a href="/tags/Ubuntu/" style="font-size: 17.38px; color: #41ba53">Ubuntu</a> <a href="/tags/Vim/" style="font-size: 18.25px; color: #35b838">Vim</a> <a href="/tags/VirtualBox/" style="font-size: 13px; color: #7dc3de">VirtualBox</a> <a href="/tags/Vsftpd/" style="font-size: 13px; color: #7dc3de">Vsftpd</a> <a href="/tags/jQuery/" style="font-size: 13px; color: #7dc3de">jQuery</a> <a href="/tags/pam-mysql/" style="font-size: 13px; color: #7dc3de">pam_mysql</a> <a href="/tags/小米/" style="font-size: 13px; color: #7dc3de">小米</a> <a href="/tags/软件/" style="font-size: 15.63px; color: #59bd8b">软件</a>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archives</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2015/09/">September 2015</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2015/08/">August 2015</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2015/07/">July 2015</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2015/05/">May 2015</a><span class="archive-list-count">5</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2015/04/">April 2015</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2015/03/">March 2015</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2015/02/">February 2015</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/12/">December 2014</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/11/">November 2014</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/10/">October 2014</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/09/">September 2014</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/08/">August 2014</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/07/">July 2014</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/06/">June 2014</a><span class="archive-list-count">6</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/05/">May 2014</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/04/">April 2014</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/03/">March 2014</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/02/">February 2014</a><span class="archive-list-count">11</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2014/01/">January 2014</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2013/12/">December 2013</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2013/11/">November 2013</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2013/08/">August 2013</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2013/07/">July 2013</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2013/06/">June 2013</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2013/05/">May 2013</a><span class="archive-list-count">5</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2013/04/">April 2013</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2012/03/">March 2012</a><span class="archive-list-count">1</span></li></ul>
    </div>
  </div>


  
    <div class="widget-wrap">
  <h3 class="widget-title">Calendar</h3>
  <div class="widget">
    <div id="g-calendar" class="g-calendar">
        <span class="g-calendar-prev"></span>
		 
        <span class="g-calendar-back"></span>
        <span class="g-calendar-now"></span>
		 
        <span class="g-calendar-next"></span>
        <div class="g-calendar-hd"></div>
        <div class="g-calendar-tit"></div>
        <div class="g-calendar-bd"></div>
    </div>
  </div>
</div>
<script type="text/javascript">
function LGY_calendar(wrapId, options){
    this.oWrap = this.getId(wrapId);
    this.oHead = this.getByClassName('g-calendar-hd',this.oWrap)[0];
    this.oBody = this.getByClassName('g-calendar-bd',this.oWrap)[0];
    this.oTit = this.getByClassName('g-calendar-tit',this.oWrap)[0];
    this.oPrev = this.getByClassName('g-calendar-prev',this.oWrap)[0];
    this.oNext = this.getByClassName('g-calendar-next',this.oWrap)[0];
    this.oNow = this.getByClassName('g-calendar-now',this.oWrap)[0];
    this.oBack = this.getByClassName('g-calendar-back',this.oWrap)[0];
    this.init();
}
LGY_calendar.prototype = {
    ///////////获取ID元素
    getId:function(id){
        return document.getElementById(id);
    },
    ////////获取css类名元素
    getByClassName:function(className,parent){
        var elem = [],
            node = parent != undefined&&parent.nodeType==1?parent.getElementsByTagName('*'):document.getElementsByTagName('*'),
            p = new RegExp("(^|\\s)"+className+"(\\s|$)");
        for(var n=0,i=node.length;n<i;n++){
            if(p.test(node[n].className)){
                elem.push(node[n]);
            }
        }
        return elem;
    },
    //填充日历
    fillDate:function(year,month){
        //本月份第一天是星期几-为显示上个月的天数做铺垫
        var first_day = new Date(year,month,1).getDay(),
        //如果刚好是星期天，则空出一行（显示上个月的天数）
            first_day = first_day == 0?first_day=7:first_day;
        //本月份最后一天是几号
        var final_date = new Date(year,month+1,0).getDate(),
        //上个月的最后一天是几号
            last_date = new Date(year,month,0).getDate(),
        //剩余的格子数--即排在末尾的格子数
            surplus = 42 - first_day - final_date;
        //设置年的链接
        var yearHead = "<a href='/" + "archives/" + year + "/'>" + year + " "+ "</a>"; 
        //设置年的链接
        var monthHead = "";
        var month1 = month + 1;
        if (month1 < 10) {
            monthHead = "<a href='/" + "archives/" + year + "/" + "0" + month1 + "/'>" + " " + month1 + " " + "</a>";
        } else {
            monthHead = "<a href='/" + "archives/" + year + "/" + month1 + "/'>" + " " + month1 + " " + "</a>";
        }
        //设置表头的日历
        this.oHead.innerHTML = yearHead+'年'+monthHead+'月';
        //填充日历执行
        var html = '';
        //上个月的显示天数
        for(var i=0;i<first_day;i++){
            html+='<span class="g-calendar-grey">'+(last_date-(first_day-1)+i)+'</span>';
        }
        //本月的显示天数
        var postdate = new Date("Mon May 26 2014 11:48:34 GMT+0800"); 
        if (true && postdate.getFullYear() == year && postdate.getMonth() == month) { 
            html += '<span>1</span><span>2</span><span>3</span><span><a href="/2014/05/04/mongodb-note-3-creating-updating-and-deleting-documents/" title="mongodb笔记(三) 创建、更新、删除文档">4</a></span><span>5</span><span>6</span><span>7</span><span><a href="/2014/05/08/mongodb-note-4-querying/" title="mongodb笔记(四) 查询">8</a></span><span>9</span><span>10</span><span>11</span><span>12</span><span>13</span><span>14</span><span>15</span><span>16</span><span>17</span><span>18</span><span>19</span><span>20</span><span>21</span><span>22</span><span>23</span><span>24</span><span>25</span><span><a href="/2014/05/26/mongodb-note-5-indexing/" title="mongodb笔记(五) 索引">26</a></span><span>27</span><span><a href="/2014/05/28/mongodb-note-6-aggregation/" title="mongodb笔记(六) 聚合">28</a></span><span>29</span><span>30</span>';
        } else {
            for(var j=0;j<final_date;j++){        
                html+='<span id="d'+(j+1)+'">'+(j+1)+'</span>';
            }
        }
        //下个月的显示天数
        for(var k=0;k<surplus;k++){
            html+='<span class="g-calendar-grey">'+(k+1)+'</span>';
        }
        //fill
        this.oBody.innerHTML = html;
        // 当前状态
        if(year==this.c_year&&this.c_month==month){
            this.oBody.getElementsByTagName('span')[first_day+this.date-1].className='g-calendar-on';
        }

        // 对所有文章遍历,然后将有文章的日期加上链接,如果文章太多的话,生成页面会很大,去掉了
        
    },
    // next切换
    next:function(){
        var _that = this;
        this.oNext.onclick = function(){
            _that.month++;
            if(_that.month>11){
                _that.month = 0;
                _that.year++;
            }
            // 填充日历
            _that.fillDate(_that.year,_that.month);
        };
    },
    // back 切换
    back:function(){
        var _that = this;
        if(this.oBack != undefined) {
            this.oBack.onclick = function(){
                var postdate = new Date("Mon May 26 2014 11:48:34 GMT+0800"); 
                _that.year = postdate.getFullYear();
                _that.month = postdate.getMonth();
                // 填充日历
                _that.fillDate(_that.year,_that.month);
            };
        }
    },
    // now切换
    now:function(){
        var _that = this;
        if(this.oNow != undefined ) {
            this.oNow.onclick = function(){
                var nowDate = new Date(); 
                _that.year = nowDate.getFullYear();
                _that.month = nowDate.getMonth();
                // 填充日历
                _that.fillDate(_that.year,_that.month);
            };
        }
    },
    // prev切换
    prev:function(){
        var _that = this;
        this.oPrev.onclick = function(){
            _that.month--;
            if(_that.month<0){
                _that.month = 11;
                _that.year--;
            }
            // 填充日历
            _that.fillDate(_that.year,_that.month);
        };
    },
    init:function(){
        this.oTit.innerHTML = '<span>日</span><span>一</span><span>二</span><span>三</span><span>四</span><span>五</span><span>六</span>';
        // 获取今天的日历时间
        var now = new Date();
        this.c_year = this.year = now.getFullYear();
        this.c_month = this.month = now.getMonth();
        this.date = now.getDate();
        // 初始化--填充日历
        this.fillDate(this.year,this.month);
        //next切换
        this.next();
        //prev切换
        this.prev();
        //back 切换
        this.back();
        //now 切换
        this.now();
    }
}
new LGY_calendar('g-calendar');
</script>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Recent Posts</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2015/09/01/javascript-summary/">JavaScript 总结</a>
          </li>
        
          <li>
            <a href="/2015/08/28/javascript-oop-function-expression-and-async/">JavaScript 面向对象程序设计、函数表达式和异步编程</a>
          </li>
        
          <li>
            <a href="/2015/08/28/javascript-reference-type/">JavaScript 引用类型</a>
          </li>
        
          <li>
            <a href="/2015/08/28/javascript-grammar/">JavaScript 基本语法</a>
          </li>
        
          <li>
            <a href="/2015/08/10/java-web/">Java Web 笔记</a>
          </li>
        
      </ul>
    </div>
  </div>


  
    
<div class="widget-wrap">
  <h3 class="widget-title">Recent Comments</h3>
  <ul class="widget ds-recent-comments" data-num-items="5" data-show-avatars="0" data-show-title="1" data-show-time="1"></ul>
</div>
<!-- 需要多说的公用代码 -->


  

</aside>

        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2015 howiefh<br>
      Powered by <a href="http://zespia.tw/hexo/" target="_blank">Hexo</a> and Theme by <a href="https://github.com/howiefh/hexo-theme-landscape-f" target="_blank" title="Landscape-F">Landscape-F</a>
    </div>
  </div>
</footer>

    </div>
    <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    

<!-- 多说公共JS代码 start (一个网页只需插入一次) -->

<script type="text/javascript">
  var duoshuoQuery = {short_name:"howiefh"};
  (function() {
	var ds = document.createElement('script');
	ds.type = 'text/javascript';ds.async = true;
	ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
	ds.charset = 'UTF-8';
	(document.getElementsByTagName('head')[0] 
		|| document.getElementsByTagName('body')[0]).appendChild(ds);
  })();
</script> 

<!-- 多说公共JS代码 end -->

<!-- 百度分享 start -->

<script>window._bd_share_config={"common":{"bdSnsKey":{},"bdText":"","bdMini":"2","bdMiniList":["mshare","douban","bdysc","sqq","qq","hi","baidu","huaban","youdao","sdo","mail","xg","diandian","fx","copy","print"],"bdPic":"","bdStyle":"1","bdSize":"16"},"share":{},"image":{"viewList":["qzone","tsina","tqq","renren","weixin"],"viewText":"分享到：","viewSize":"16"}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];</script>

<!-- 百度分享 end -->

<!--
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>
<script src="/js/jquery.min.js"></script>
-->
<script src="/js/jquery.min.js" type="text/javascript"></script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css" type="text/css">
  <script src="/fancybox/jquery.fancybox.pack.js" type="text/javascript"></script>


<div class="bottom-btn">

	<a class="icon-gotop" href="javascript:void(0)" title="返回顶部"></a>
	<script src="/js/gotop.js" type="text/javascript"></script>
	<!--
	<script src="/js/gotop.js"></script>
	-->


	<a class="icon-toc-toggle" href="javascript:void(0)" title="文章目录"></a>
	<!--
	<script src="/js/toc_aside_toggle.js"></script>
	-->

</div>
<script src="/js/toc_aside_toggle.js" type="text/javascript"></script>


<script src="/js/script.js" type="text/javascript"></script>

  </div>
</body>
</html>
